package com.weixingtu.commuterCar.dao.impl;

import com.weixingtu.commuterCar.bean.Point;
import com.weixingtu.commuterCar.dao.PointDao;
import com.weixingtu.commuterCar.util.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PointDaoMysql implements PointDao {
    /**
     * 用于查询数据库中的所有站点信息
     */

    public static final String SQL_FIND_ALL = " SELECT * FROM point ORDER BY obj_id+0 ";
    /**
     * 用于查询数据库中的所属路线站点信息
     */
    public static final String SQL_FIND_BY_LINE = " SELECT * FROM point WHERE line_id=? ORDER BY obj_id+0 ";

    /**
     * 用于查询所有站点
     *
     * @return 站点的集合
     */
    @Override
    public List<Point> findAll() {
        List<Point> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
//        if(conn == null) return null;
        //2.    预编译SQL语句
        try {
            if(conn == null) {
                System.out.println("数据库连接失败");
            } else{
                System.out.println("数据库连接成功");
            }
            state = conn.prepareStatement(SQL_FIND_ALL);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while (result.next()) {
                //  int objId, int pointId, String pointName, double lat, double lng, double costTime, int lineId, String lineName
                int objId = Integer.parseInt(result.getString("obj_id"));
                double lat = Double.parseDouble(result.getString("lat"));
                double lng = Double.parseDouble(result.getString("lng"));
                int lineId = Integer.parseInt(result.getString("line_id"));
                String lineName = result.getString("line_name");
                Point point = new Point(objId, lat, lng, lineId, lineName);
                data.add(point);
            }
            return data;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return null;
    }

    /**
     * 根据路线查询站点
     *
     * @param lineId
     * @return 所属路线的站点的集合
     */
    @Override
    public List<Point> findByLine(int lineId) {
        List<Point> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.    预编译SQL语句
        try {
            if(conn == null) {
                System.out.println("数据库连接失败");
            } else{
                System.out.println("数据库连接成功");
            }
            state = conn.prepareStatement(SQL_FIND_BY_LINE);
            String s = String.valueOf(lineId);
            if(s==null || s.length()==0) return null;
            //3.    填充参数(可选)
            state.setString(1, s);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while (result.next()) {
                int objId = Integer.parseInt(result.getString("obj_id"));
                double lat = Double.parseDouble(result.getString("lat"));
                double lng = Double.parseDouble(result.getString("lng"));
//                int lineId = Integer.parseInt(result.getString("line_id"));
                String lineName = result.getString("line_name");
                Point point = new Point(objId,  lat, lng, lineId, lineName);
                data.add(point);
            }
            return data;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return null;
    }
}
